CREATE TABLE grades1 (
  id int NOT NULL ,
  name varchar(20) DEFAULT NULL,
  subject varchar(20) DEFAULT NULL,
  score int DEFAULT NULL,

)

INSERT INTO grades1 VALUES (1, 'lyy', '英语', 100);
INSERT INTO grades1 VALUES (2, 'lyy', '数学', 98);
INSERT INTO grades1 VALUES (3, 'lyy', '语文', 97);
INSERT INTO grades1 VALUES (4, 'gss', '英语', 89);
INSERT INTO grades1 VALUES (5, 'gss', '数学', 88);
INSERT INTO grades1 VALUES (6, 'gss', '语文', 87);
INSERT INTO grades1 VALUES (7, 'liming', '英语', 79);
INSERT INTO grades1 VALUES (8, 'liming', '数学', 77);
INSERT INTO grades1 VALUES (9, 'liming', '语文', 75);


--列转行
select score from grades1 where name = 'lyy'
union
select score from grades1 where name = 'gss'
union
select score from grades1 where name = 'liming'

create view vgg
as
select name,subject,score from grades1
go
select * from vgg pivot(sum(score) for subject in ([英语],[数学],[语文]))b

use BankTest
--假设刘备取款6000，(添加check约束，设置账户余额必须>=0)，要求：使用事务实现，修改余额和添加取款记录两步操作使用事务

declare @money int=6000
declare @name varchar(20)='刘备'
declare @CardNo int=(select CardNo from Bankcard where AccountId=(select AccountId from AccountInfo where RealName=@name))
declare @myerr int=0

begin transaction a

alter table BankCard add constraint lala check(CardMoney>=0) 
update BankCard set CardMoney-=@money where CardNo=@CardNo
set @myerr+=@@ERROR
insert into CardExchange (CardNo,MoneyOutBank,ExchangeTime) values (@CardNo,@money,GETDATE())
set @myerr+=@@ERROR

if(@myerr=0)
begin
	commit transaction a
end
else
begin
	rollback transaction a
end

--刘备向张飞转账1000元，(添加check约束，设置账户余额必须>=0)

select * from BankCard
select * from CardTransfer
select * from AccountInfo

declare @money int=1000
declare @outname varchar(20)='刘备'
declare @inname varchar(20)='张飞'
declare @CardoutNo int=(select CardNo from Bankcard where AccountId=(select AccountId from AccountInfo where RealName=@outname))
declare @CardinNo int=(select CardNo from Bankcard where AccountId=(select AccountId from AccountInfo where RealName=@inname))
declare @myerr int=0

begin transaction b

alter table BankCard add constraint lala check(CardMoney>=0) 
set @myerr+=@@ERROR
update BankCard set CardMoney-=@money where CardNo=@CardoutNo
set @myerr+=@@ERROR
update BankCard set CardMoney+=@money where CardNo=@CardinNo
set @myerr+=@@ERROR
insert into CardTransfer(CardNoOut,CardNoIn,TransferMoney,TransferTime) values (@CardoutNo,@CardinNo,@money,GETDATE())
set @myerr+=@@ERROR

if(@myerr=0)
begin
	commit transaction a
end
else
begin
	rollback transaction a
end